﻿CREATE PROCEDURE [dbo].[GetAccountActivities]
	@AccountIds NVARCHAR(500),
	@FromDate DATETIME,
	@ToDate DATETIME
AS
BEGIN
	SELECT p.TransactionId AS TransactionId, t.Date AS Date, Explanation, SUM(Debits) AS Debits, SUM(Credits) AS Credits FROM (

		SELECT Amount AS Debits, 0 AS Credits, TransactionId FROM TransactionDebits
		WHERE @AccountIds LIKE '%,' + CONVERT(NVARCHAR, AccountId) + ',%'
	UNION
		SELECT 0 AS Debits, Amount AS Credits, TransactionId FROM TransactionCredits
		WHERE @AccountIds LIKE '%,' + CONVERT(NVARCHAR, AccountId) + ',%'

	) p JOIN Transactions t ON p.TransactionId = Id
	WHERE t.Date >= @FromDate AND  t.Date <= @ToDate
	GROUP BY TransactionId, Explanation, Date
	ORDER BY Date
END